﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dapper;
using Models;
using Common;
using System.Data.SqlClient;
using System.Data;

namespace DbControl
{
    public class OprSensor
    {
        public static List<sensors> GetSensorAll()
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"SELECT id
                          ,sensor_code
                          ,sensor_name
                          ,sensor_sn
                          ,gateway_id
                          ,sensor_type
                          ,sensor_mode
                          ,sensor_topic
                          ,sensor_subscribe
                          ,flag
                          ,online_status
                      FROM sensors WHERE flag>0 ";
                    return connection.Query<sensors>(sql).ToList();
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return null;
            }
        }
        public static DataTable GetSensorByName(string Name)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = "";
                if (string.IsNullOrEmpty(Name))
                {
                    sql = @"SELECT id
                          ,sensor_code
                          ,sensor_name
                          ,sensor_sn
                          ,gateway_id
                          ,sensor_type
                          ,sensor_mode
                          ,sensor_topic
                          ,sensor_subscribe
                          ,flag
                          ,online_status
                      FROM sensors";
                }
                else
                {
                    sql = string.Format(@"SELECT id
                          ,sensor_code
                          ,sensor_name
                          ,sensor_sn
                          ,gateway_id
                          ,sensor_type
                          ,sensor_mode
                          ,sensor_topic
                          ,sensor_subscribe
                          ,flag
                          ,online_status
                      FROM sensors  WHERE  sensor_name='{0}'", Name);
                }
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        public static sensors GetSensorById(int id)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT id
                          ,sensor_code
                          ,sensor_name
                          ,sensor_sn
                          ,gateway_id
                          ,sensor_type
                          ,sensor_mode
                          ,sensor_topic
                          ,sensor_subscribe
                          ,flag
                          ,online_status
                      FROM sensors  WHERE   id=@id";
                return connection.Query<sensors>(sql, new { id = id }).SingleOrDefault();
            }
        }
        public static sensors GetSensorByCode(string code)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT id
                          ,sensor_code
                          ,sensor_name
                          ,sensor_sn
                          ,gateway_id
                          ,sensor_type
                          ,sensor_mode
                          ,sensor_topic
                          ,sensor_subscribe
                          ,flag
                          ,online_status
                      FROM sensors  WHERE   sensor_code=@sensor_code";
                return connection.Query<sensors>(sql, new { sensor_code = code }).SingleOrDefault();
            }
        }
        public static bool IsExistSensorCode(string sensor_code)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"select COUNT(id)  from sensors WHERE  sensor_code =@sensor_code ";
                    intNum = (int)connection.ExecuteScalar(sql, new { sensor_code = sensor_code });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static bool IsExistSensorName(string sensor_name)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"select COUNT(id)  from sensors WHERE  sensor_name =@sensor_name ";
                    intNum = (int)connection.ExecuteScalar(sql, new { sensor_name = sensor_name });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static bool IsExistSensortopic(string sensor_topic)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"select COUNT(id)  from sensors WHERE  sensor_topic =@sensor_topic ";
                    intNum = (int)connection.ExecuteScalar(sql, new { sensor_topic = sensor_topic });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static bool IsExistSensorsubscribe(string sensor_subscribe)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"select COUNT(id)  from sensors WHERE  sensor_subscribe =@sensor_subscribe ";
                    intNum = (int)connection.ExecuteScalar(sql, new { sensor_subscribe = sensor_subscribe });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static int DeleteById(int id)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"DELETE FROM sensors WHERE id=@id";
                    sql = @"UPDATE sensors SET flag = 0 WHERE id=@id";
                    return connection.Execute(sql, new { id = id });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int Update(sensors dv)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"UPDATE sensors
                                   SET sensor_code= @sensor_code 
                                      ,sensor_name= @sensor_name 
                                      ,sensor_sn= @sensor_sn 
                                      ,gateway_id= @gateway_id 
                                      ,sensor_type= @sensor_type 
                                      ,sensor_mode= @sensor_mode 
                                      ,sensor_topic= @sensor_topic 
                                      ,sensor_subscribe= @sensor_subscribe 
                                      ,flag= @flag 
                                      ,online_status= @online_status                                      
                                 WHERE id=@id";
                    return connection.Execute(sql, dv);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int Insert(sensors dv)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"INSERT INTO sensors
                                   (sensor_code
                                   ,sensor_name
                                   ,sensor_sn
                                   ,gateway_id
                                   ,sensor_type
                                   ,sensor_mode
                                   ,sensor_topic
                                   ,sensor_subscribe
                                   ,flag
                                   ,online_status)
                             VALUES
                                   (@sensor_code
                                   ,@sensor_name
                                   ,@sensor_sn
                                   ,@gateway_id
                                   ,@sensor_type
                                   ,@sensor_mode
                                   ,@sensor_topic
                                   ,@sensor_subscribe
                                   ,@flag
                                   ,@online_status)";
                    return connection.Execute(sql, dv);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        //更新终端状态
        public static void SetOnLineStatue(string sensor_code, bool online_status)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = "";
                    if (online_status)
                        sql = @"UPDATE sensors
                                   SET online_status=1                                      
                                 WHERE  sensor_code= @sensor_code ";
                    else
                        sql = @"UPDATE sensors
                                   SET online_status=0                                      
                                 WHERE  sensor_code= @sensor_code ";
                    connection.Execute(sql, new { sensor_code = sensor_code });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.Message.ToString());
            }
        }
    }
}
